You can create custom duplicate match key formulas to prevent the entry of duplicate customer records in the Name table. One formula, the Duplicate Record Check Formula, affects records entered anywhere in iMIS except in the Customer Portfolio. The CP Duplicate Record Check Formula is specific to the Customer Portfolio. In order for duplicates to be detected properly, these two formulas must be equivalent.
Your formulas can combine fields or portions of fields to compare a record being entered against records that already exist in the database. When a possible match is found, iMIS presents a match list and warns the data entry user that the record may be a duplicate. Based on this information, the user can continue or can cancel the insertion.
Default Formulas
The default CP Duplicate Record Check Formula specifies the same criteria as the Duplicate Record Check Formula, but uses SQL instead of Omnis.
By default, the Duplicate Record Check Formula field contains the following Omnis formula:
upp(con(mid(Name.ZIP,1,5),pick(Name.LAST_NAME<>'',mid(Name.COMPANY,1,5),con(mid(Name.LAST_NAME,1,4),mid(Name.FIRST_NAME,1,1)))))
By default, the CP Duplicate Record Check Formula field contains the following SQL formula:
UPPER(SUBSTRING(Name.ZIP,1,5) + (CASE WHEN Name.LAST_NAME<>'' THEN (SUBSTRING(Name.LAST_NAME,1,4) + SUBSTRING(Name.FIRST_NAME,1,1)) ELSE SUBSTRING(Name.COMPANY,1,5) END))
Caution! If you customize the Duplicate Record Check Formula and CP Duplicate Record Check Formula ensure that the formulas contain equivalent selection criteria. Although it is possible to specify different criteria in these fields, if you do not use equivalent formulas, identical records entered through different modules will end up with different duplicate match keys and will therefore not be identified by iMIS as potential duplicates.
When entering a custom formula in the CP Duplicate Record Check Formula field, the formula can contain up to 255 characters, and the resulting duplicate match key can contain up to 20 characters. You can use the following Name fields when entering a custom duplicate check formula in the CP Duplicate Record Check Formula field:
Name.FIRST_NAME |
Name.LAST_NAME |
Name.COMPANY |
Name.ZIP |
Name.CITY |
Name.STATE_PROVINCE |
Name.COUNTRY |
Name.ORG_CODE |
Name.MEMBER_TYPE |
Name.MAJOR_KEY |
Name.WORK_PHONE |
Name.HOME_PHONE |
Name.FAX |
Name.EMAIL |
Name.COUNTY |
Name.TOLL_FREE |
Name.CHAPTER |
Name.JOIN_DATE |
Name.BIRTH_DATE |
Name.GENDER |
Note: This facility cannot protect against all data-entry problems, such as address changes, variable name spellings, and typing errors. For best results, use standard lookup before adding questionable database records.
10.6 Production Release. Updated 1/18/2006 3:26:46 PM
© Copyright by Advanced Solutions International, Inc.
All rights reserved.